﻿----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
IF SCHEMA_ID(N'_DB') IS NULL
	EXECUTE(N'CREATE SCHEMA [_DB] AUTHORIZATION [dbo]')
GO

----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'[_DB].[Function_Exists]', N'P') IS NOT NULL
	DROP PROCEDURE [_DB].[Function_Exists]
GO

----------------------------------------------------------------------------------------------------
--
--  Check the existance of the user-defined function.
--
--  INPUT
--		@@Name
--		Name of the user-defined function to be checked.
--
----------------------------------------------------------------------------------------------------
CREATE FUNCTION [_DB].[Function_Exists] (
	@@Name [sysname]
)
RETURNS [bit]
AS
BEGIN
	RETURN CASE
		WHEN OBJECT_ID(@@Name, 'FN') IS NOT NULL THEN 1
		WHEN OBJECT_ID(@@Name, 'TF') IS NOT NULL THEN 1
		WHEN OBJECT_ID(@@Name, 'IF') IS NOT NULL THEN 1
		WHEN OBJECT_ID(@@Name, 'AF') IS NOT NULL THEN 1
		WHEN OBJECT_ID(@@Name, 'FS') IS NOT NULL THEN 1
		WHEN OBJECT_ID(@@Name, 'FT') IS NOT NULL THEN 1
		ELSE 0
	END
END
GO

----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'[_DB].[Function_Drop]', N'P') IS NOT NULL
	DROP PROCEDURE [_DB].[Function_Drop]
GO

----------------------------------------------------------------------------------------------------
--
--  User-defined function dropping routine.
--
--  INPUT
--		@@Name
--		Name of the user-defined function to be dropped.
--
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE [_DB].[Function_Drop]
	@@Name [sysname]
AS
SET NOCOUNT ON

IF [_DB].[Function_Exists](@@Name) = 1
BEGIN
	DECLARE @SQL [nvarchar](max) SELECT @SQL =
		N'DROP FUNCTION ' + @@Name

	EXECUTE(@SQL)
END

RETURN 0
GO



----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
EXECUTE [_DB].[Function_Drop]
	@@Name = '[_DB].[Procedure_Exists]'
GO

----------------------------------------------------------------------------------------------------
--
--  Check the existance of the stored procedure.
--
--  INPUT
--		@@Name
--		Name of the stored procedure to be checked.
--
----------------------------------------------------------------------------------------------------
CREATE FUNCTION [_DB].[Procedure_Exists] (
	@@Name [sysname]
)
RETURNS [bit]
AS
BEGIN
	RETURN CASE
		WHEN OBJECT_ID(@@Name, 'P') IS NOT NULL THEN 1
		ELSE 0
	END
END
GO

----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
IF [_DB].[Procedure_Exists](N'[_DB].[Procedure_Drop]') = 1
	DROP PROCEDURE [_DB].[Procedure_Drop]
GO

----------------------------------------------------------------------------------------------------
--
--  Stored procedure dropping routine.
--
--  INPUT
--		@@Name
--		Name of the stored procedure to be dropped.
--
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE [_DB].[Procedure_Drop]
	@@Name [sysname]
AS
SET NOCOUNT ON

IF [_DB].[Procedure_Exists](@@Name) = 1
BEGIN
	DECLARE @SQL [nvarchar](max) SELECT @SQL =
		N'DROP PROCEDURE ' + @@Name

	EXECUTE(@SQL)
END

RETURN 0
GO


----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'[_DB].[Schema_Exists]', 'FN') IS NOT NULL
	DROP FUNCTION [_DB].[Schema_Exists]
GO

----------------------------------------------------------------------------------------------------
--
--  Check the existance of the schema.
--
--  INPUT
--		@@Name
--		Name of the schema to be checked.
--
----------------------------------------------------------------------------------------------------
CREATE FUNCTION [_DB].[Schema_Exists] (
	@@Name [sysname]
)
RETURNS [bit]
AS
BEGIN
	RETURN CASE
		WHEN SCHEMA_ID(QUOTENAME(@@Name)) IS NULL THEN 0
		ELSE 1
	END
END
GO

----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'[_DB].[Schema_Create]', 'P') IS NOT NULL
	DROP PROCEDURE [_DB].[Schema_Create]
GO

----------------------------------------------------------------------------------------------------
--
--  Schema creating routine.
--
--  INPUT
--		@@Name
--		Name of the schema to be created.
--
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE [_DB].[Schema_Create]
	@@Name [sysname]
AS
SET NOCOUNT ON

IF [_DB].[Schema_Exists](@@Name) = 0 BEGIN
	DECLARE @SQL [nvarchar](max) SELECT @SQL =
		N'CREATE SCHEMA ' + QUOTENAME(@@Name) + ' AUTHORIZATION [dbo]'
	
	EXECUTE(@SQL)
END
GO


----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'[_DB].[Schema_Drop]', 'P') IS NOT NULL
	DROP PROCEDURE [_DB].[Schema_Drop]
GO

----------------------------------------------------------------------------------------------------
--
--  Schema dropping routine.
--
--  INPUT
--		@@Name
--		Name of the schema to be dropped.
--
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE [_DB].[Schema_Drop]
	@@Name [sysname]
AS
SET NOCOUNT ON

IF [_DB].[Schema_Exists](@@Name) = 1 BEGIN
	DECLARE @SQL [nvarchar](max) SELECT @SQL =
		N'DROP SCHEMA ' + QUOTENAME(@@Name)
	
	EXECUTE(@SQL)
END
GO



----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
EXECUTE [_DB].[Function_Drop]
	@@Name = '[_DB].[View_Exists]'
GO

----------------------------------------------------------------------------------------------------
--
--  Check the existance of the view.
--
--  INPUT
--		@@Name
--		Name of the view to be checked.
--
----------------------------------------------------------------------------------------------------
CREATE FUNCTION [_DB].[View_Exists] (
	@@Name [sysname]
)
RETURNS [bit]
AS
BEGIN
	RETURN CASE
		WHEN OBJECT_ID(@@Name, 'V') IS NOT NULL THEN 1
		ELSE 0
	END
END
GO

----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
EXECUTE [_DB].[Procedure_Drop]
	@@Name = '[_DB].[View_Drop]'
GO

----------------------------------------------------------------------------------------------------
--
--  View dropping routine.
--
--  INPUT
--		@@Name
--		Name of the view to be dropped.
--
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE [_DB].[View_Drop]
	@@Name [sysname]
AS
SET NOCOUNT ON

IF [_DB].[View_Exists](@@Name) = 1
BEGIN
	DECLARE @SQL [nvarchar](max) SELECT @SQL =
		N'DROP VIEW ' + @@Name

	EXECUTE(@SQL)
END

RETURN 0
GO



----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
CREATE TABLE [_DB].[Translit] (
	-- Source char to be transliterated.
	[Char] [binary](2) NOT NULL,

	-- Transliterated chars.
	[Translit] [varchar](4) NOT NULL
)
GO

-- Primary key -------------------------------------------------------------------------------------
ALTER TABLE
	[_DB].[Translit]
ADD CONSTRAINT
	[PK_DB_Translit]
PRIMARY KEY CLUSTERED (
	[Char] ASC
)
GO

SET NOCOUNT ON
GO

INSERT INTO
	[_DB].[Translit]
SELECT TOP 0
	NULL AS [Char],
	NULL AS [Translit]

	-- Russian -------------------------------------------------------------------------------------
UNION ALL SELECT CONVERT([binary](2), N'а'), 'a'
UNION ALL SELECT CONVERT([binary](2), N'б'), 'b'
UNION ALL SELECT CONVERT([binary](2), N'в'), 'v'
UNION ALL SELECT CONVERT([binary](2), N'г'), 'g'
UNION ALL SELECT CONVERT([binary](2), N'д'), 'd'
UNION ALL SELECT CONVERT([binary](2), N'е'), 'e'
UNION ALL SELECT CONVERT([binary](2), N'ё'), 'e'
UNION ALL SELECT CONVERT([binary](2), N'ж'), 'j'
UNION ALL SELECT CONVERT([binary](2), N'з'), 'z'
UNION ALL SELECT CONVERT([binary](2), N'и'), 'i'
UNION ALL SELECT CONVERT([binary](2), N'й'), 'y'
UNION ALL SELECT CONVERT([binary](2), N'к'), 'k'
UNION ALL SELECT CONVERT([binary](2), N'л'), 'l'
UNION ALL SELECT CONVERT([binary](2), N'м'), 'm'
UNION ALL SELECT CONVERT([binary](2), N'н'), 'n'
UNION ALL SELECT CONVERT([binary](2), N'о'), 'o'
UNION ALL SELECT CONVERT([binary](2), N'п'), 'p'
UNION ALL SELECT CONVERT([binary](2), N'р'), 'r'
UNION ALL SELECT CONVERT([binary](2), N'с'), 's'
UNION ALL SELECT CONVERT([binary](2), N'т'), 't'
UNION ALL SELECT CONVERT([binary](2), N'у'), 'u'
UNION ALL SELECT CONVERT([binary](2), N'ф'), 'f'
UNION ALL SELECT CONVERT([binary](2), N'х'), 'h'
UNION ALL SELECT CONVERT([binary](2), N'ц'), 'c'
UNION ALL SELECT CONVERT([binary](2), N'ч'), 'ch'
UNION ALL SELECT CONVERT([binary](2), N'ш'), 'sh'
UNION ALL SELECT CONVERT([binary](2), N'щ'), 'shh'
UNION ALL SELECT CONVERT([binary](2), N'ь'), ''
UNION ALL SELECT CONVERT([binary](2), N'ы'), 'y'
UNION ALL SELECT CONVERT([binary](2), N'ъ'), ''
UNION ALL SELECT CONVERT([binary](2), N'э'), 'e'
UNION ALL SELECT CONVERT([binary](2), N'ю'), 'u'
UNION ALL SELECT CONVERT([binary](2), N'я'), 'ya'

UNION ALL SELECT CONVERT([binary](2), N'А'), 'A'
UNION ALL SELECT CONVERT([binary](2), N'Б'), 'B'
UNION ALL SELECT CONVERT([binary](2), N'В'), 'V'
UNION ALL SELECT CONVERT([binary](2), N'Г'), 'G'
UNION ALL SELECT CONVERT([binary](2), N'Д'), 'F'
UNION ALL SELECT CONVERT([binary](2), N'Е'), 'E'
UNION ALL SELECT CONVERT([binary](2), N'Ё'), 'E'
UNION ALL SELECT CONVERT([binary](2), N'Ж'), 'J'
UNION ALL SELECT CONVERT([binary](2), N'З'), 'Z'
UNION ALL SELECT CONVERT([binary](2), N'И'), 'I'
UNION ALL SELECT CONVERT([binary](2), N'Й'), 'Y'
UNION ALL SELECT CONVERT([binary](2), N'К'), 'K'
UNION ALL SELECT CONVERT([binary](2), N'Л'), 'L'
UNION ALL SELECT CONVERT([binary](2), N'М'), 'M'
UNION ALL SELECT CONVERT([binary](2), N'Н'), 'N'
UNION ALL SELECT CONVERT([binary](2), N'О'), 'O'
UNION ALL SELECT CONVERT([binary](2), N'П'), 'P'
UNION ALL SELECT CONVERT([binary](2), N'Р'), 'R'
UNION ALL SELECT CONVERT([binary](2), N'С'), 'S'
UNION ALL SELECT CONVERT([binary](2), N'Т'), 'T'
UNION ALL SELECT CONVERT([binary](2), N'У'), 'U'
UNION ALL SELECT CONVERT([binary](2), N'Ф'), 'F'
UNION ALL SELECT CONVERT([binary](2), N'Х'), 'H'
UNION ALL SELECT CONVERT([binary](2), N'Ц'), 'C'
UNION ALL SELECT CONVERT([binary](2), N'Ч'), 'Ch'
UNION ALL SELECT CONVERT([binary](2), N'Ш'), 'Sh'
UNION ALL SELECT CONVERT([binary](2), N'Щ'), 'Shh'
UNION ALL SELECT CONVERT([binary](2), N'Ь'), ''
UNION ALL SELECT CONVERT([binary](2), N'Ы'), 'Y'
UNION ALL SELECT CONVERT([binary](2), N'Ъ'), ''
UNION ALL SELECT CONVERT([binary](2), N'Э'), 'E'
UNION ALL SELECT CONVERT([binary](2), N'Ю'), 'U'
UNION ALL SELECT CONVERT([binary](2), N'Я'), 'Ya'
GO

SET NOCOUNT OFF
GO

----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
EXECUTE [_DB].[Function_Drop] @@Name = N'[_DB].[String_TranslitUrl]'
GO

----------------------------------------------------------------------------------------------------
--
--	Translit string to URL.
--
----------------------------------------------------------------------------------------------------
CREATE FUNCTION [_DB].[String_TranslitUrl] (
	@@Value [nvarchar](max)
)
RETURNS [varchar](max)
AS
BEGIN
	DECLARE @Result [varchar](max) SET @Result = ''
	DECLARE @Source [varbinary](max) SET @Source = CONVERT([varbinary](max), @@Value)
	DECLARE @Index [int] SET @Index = 1
	DECLARE @UCS [binary](2) SET @UCS = NULL
	DECLARE @Char [nvarchar](4) SET @Char = NULL
	DECLARE @Delimiter [bit] SET @Delimiter = 0
	
	WHILE @Index <= LEN(@Source) BEGIN
		SELECT
			@UCS = SUBSTRING(@Source, @Index, 2),
			@Char = CONVERT([nchar](1), @UCS)

		IF @Char LIKE '[A-Z0-9]' BEGIN
			SET @Result = @Result + @Char
			SET @Delimiter = 0
		END ELSE BEGIN
			SELECT
				@Char = NULL

			SELECT
				@Char = [Translit]
			FROM
				[_DB].[Translit]
			WHERE
				[Char] = @UCS

			IF @Char IS NOT NULL BEGIN
				SET @Result = @Result + @Char
				SET @Delimiter = 0
			END ELSE IF @Delimiter = 0 BEGIN
				SET @Result = @Result + '-'
				SET @Delimiter = 1
			END
		END

		SET @Index = @Index + 2
	END
	
	IF RIGHT(@Result, 1) = '-' BEGIN
		SET @Result = LEFT(@Result, LEN(@Result) - 1)
	END

	RETURN @Result
END
GO

----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
EXECUTE [_DB].[Function_Drop] @@Name = N'[_DB].[String_FormatXML]'
GO

----------------------------------------------------------------------------------------------------
--
--	Format string using parameters, described in XML.
--
--	SAMPLE
--		SELECT [dbo].[String_Format](
--			'Welcome %first %last!',
--			(
--				SELECT
--					'Stanislav' AS [first],
--					'Yarmonov' AS [last]
--				FOR XML
--					PATH('')
--			)
--		)
--
----------------------------------------------------------------------------------------------------
CREATE FUNCTION [_DB].[String_FormatXML] (
	@@Format [nvarchar](max),
	@@Params [xml]
)
RETURNS [nvarchar](max)
AS
BEGIN
	DECLARE @Result [nvarchar](max) SET @Result = @@Format
	SELECT
		@Result = REPLACE(
			@Result,
			'%' + [T].[c].value('local-name(.)', 'varchar(128)'),
			[T].[c].value('.[1]', 'nvarchar(max)')
		)
	FROM
		@@Params.nodes('*') AS [T](c)

	RETURN @Result
END
GO


----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
CREATE TABLE [_DB].[LogType] (
	-- Log type identifier.
	[ID] [int] IDENTITY(1,1) NOT NULL,

	-- Log type name.
	[Name] [varchar](127) NOT NULL,

	-- Log type descriptiotn.
	[Description] [nvarchar](max) NULL
)
GO

-- Primary key -------------------------------------------------------------------------------------
ALTER TABLE
	[_DB].[LogType]
ADD CONSTRAINT
	[PK_DB_LogType]
PRIMARY KEY NONCLUSTERED (
	[ID] ASC
)
GO

-- Unique clustere index for name ------------------------------------------------------------------
CREATE UNIQUE CLUSTERED INDEX
	[UX_DB_LogType_Name]
ON [_DB].[LogType] (
	[Name] ASC
)
GO

----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
EXECUTE [_DB].[Procedure_Drop] N'[_DB].[LogType_Register]'
GO

----------------------------------------------------------------------------------------------------
--
--  LogType registration routine.
--
--  INPUT
--		@@Name
--		Name of the log type.
--
--		@@Description
--		Log type description.
--
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE [_DB].[LogType_Register]
	@@Name [varchar](128),
	@@Description [nvarchar](max)
AS
SET NOCOUNT ON

INSERT INTO [_DB].[LogType] (
	[Name],
	[Description]
) SELECT
	@@Name,
	@@Description

RETURN 0
GO


----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
CREATE TABLE [_DB].[Log] (
	-- Log record identifier.
	[ID] [timestamp] NOT NULL,

	-- Date and time, when log record was created.
	[Created] [datetime] NOT NULL,

	-- Log type.
	[Type] [int] NULL,

	-- Log message.
	[Message] [nvarchar](255) NOT NULL,

	-- Log details.
	[Details] [xml] NULL
)
GO

-- Primary key -------------------------------------------------------------------------------------
ALTER TABLE
	[_DB].[Log]
ADD CONSTRAINT
	[PK_DB_Log]
PRIMARY KEY NONCLUSTERED (
	[ID] ASC
)
GO

-- Foreign key to error ----------------------------------------------------------------------------
ALTER TABLE
	[_DB].[Log]
ADD CONSTRAINT
	[FK_DB_Log_Type]
FOREIGN KEY (
	[Type]
) REFERENCES [_DB].[LogType] (
	[ID]
)
GO

----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
EXECUTE [_DB].[Procedure_Drop] N'[_DB].[Log_Write]'
GO

----------------------------------------------------------------------------------------------------
--
--  Log record registration routine.
--
--  INPUT
--		@@Type
--		Type of the log record.
--
--		@@Message
--		Logged message.
--
--		@@Details
--		Log record details.
--
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE [_DB].[Log_Write]
	@@Type [int] = NULL,
	@@Message [nvarchar](255),
	@@Details [xml] = NULL
AS
SET NOCOUNT ON

INSERT INTO [_DB].[Log] (
	[Created],
	[Type],
	[Message],
	[Details]
) SELECT
	GETDATE(),
	@@Type,
	@@Message,
	@@Details

RETURN 0
GO


----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
EXECUTE [_DB].[Procedure_Drop] N'[_DB].[Log_Clear]'
GO

----------------------------------------------------------------------------------------------------
--
--  Log clear routine.
--
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE [_DB].[Log_Clear]
AS
SET NOCOUNT ON

--TODO:Truncate or delete?
TRUNCATE TABLE
	[_DB].[Log]

RETURN 0
GO



----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------
EXECUTE [_DB].[Procedure_Drop] N'[_DB].[Debug_StringTruncate]'
GO

----------------------------------------------------------------------------------------------------
--
--  Debug string truncation routines.
--
--  INPUT
--		@@Name
--		Name of the table to create the debug routines.
--
--		@@Remove
--		Values 1 if removing the debugging.
--
--		@@Errors
--		Errors to be logged.
--
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE [_DB].[Debug_StringTruncate]
	@@Name [sysname],
	@@Remove [bit] = 0,
	@@Errors [int] = 1,
	@@ViewName [sysname] = NULL OUTPUT,
	@@TriggerName [sysname] = NULL OUTPUT
AS
SET NOCOUNT ON

-- Detect table identifier -------------------------------------------------------------------------
DECLARE @ObjectID [int] SET @ObjectID = OBJECT_ID(@@Name, 'U')
--TODO:Check for existance

-- Prepare view and trigger names ------------------------------------------------------------------
DECLARE @TableName [sysname] SET @TableName = NULL
--TODO:Move debug objectto separate temp schema _DBG and move Debug_... routines there?
SELECT
	@TableName = QUOTENAME(SCHEMA_NAME([schema_id])) + '.' + QUOTENAME([name]),
	@@ViewName = ISNULL(@@ViewName, QUOTENAME(SCHEMA_NAME([schema_id]))	+ '.' + QUOTENAME([name] + '#ViewStringTruncate')),
	@@TriggerName = ISNULL(@@TriggerName, QUOTENAME(SCHEMA_NAME([schema_id])) + '.' + QUOTENAME([name] + '#OnStringTruncate'))
FROM
	[sys].[tables]
WHERE
	[object_id] = @ObjectID

-- Clear objects if exists -------------------------------------------------------------------------
EXECUTE [_DB].[View_Drop] @@Name = @@ViewName

-- View creation -----------------------------------------------------------------------------------
IF @@Remove = 1 RETURN 0

DECLARE @SQL [nvarchar](max) SET @SQL = ''

SELECT
	@SQL = @SQL
	+ CHAR(13) + CHAR(10)
	+ CASE
		WHEN [columns].[column_id] = 1 THEN ''
		ELSE ','
	END
	+ CASE
		WHEN [types].[name] IN ('varchar', 'char', 'nvarchar', 'nchar', 'varbinary', 'binary') AND [columns].[max_length] > 0 THEN
			'CONVERT('
			+ CASE [types].[name]
				WHEN 'varchar' THEN '[varchar](max)'
				WHEN 'char' THEN '[varchar](max)'
				WHEN 'nvarchar' THEN '[nvarchar](max)'
				WHEN 'nchar' THEN '[nvarchar](max)'
				WHEN 'varbinary' THEN '[varbinary](max)'
				WHEN 'binary' THEN '[varbinary](max)'
			END
			+ ', '
			+ QUOTENAME([columns].[name])
			+ ') AS ' + QUOTENAME([columns].[name])
		ELSE QUOTENAME([columns].[name])
	END
FROM
	[sys].[columns] as [columns]
	INNER JOIN [sys].[types] ON ([columns].[system_type_id] = [types].[system_type_id])
WHERE
	[object_id] = @ObjectID
ORDER BY
	[column_id] ASC

SELECT @SQL = [_DB].[String_FormatXML](
	N'CREATE VIEW %view
	AS
		SELECT
			%fields
		FROM
			%table
	',
	(
		SELECT
			@TableName AS [table],
			@@ViewName AS [view],
			@SQL AS [fields]
		FOR
			XML PATH('')
	)
)

EXECUTE(@SQL)

-- Trigger creation --------------------------------------------------------------------------------
SET @SQL = ''
SELECT
	@SQL = @SQL
	+ CHAR(13) + CHAR(10)
	+ [_DB].[String_FormatXML](
		N'UNION ALL SELECT ''%name'', DATALENGTH(%name), %max, %name FROM [inserted] WHERE DATALENGTH(%name) > %max',
		(
			SELECT
				[columns].[max_length] AS [max],
				QUOTENAME([columns].[name]) AS [name]
			FOR
				XML PATH('')
		)
	)
FROM
	[sys].[columns] as [columns]
	INNER JOIN [sys].[types] ON ([columns].[system_type_id] = [types].[system_type_id])
WHERE
	[object_id] = OBJECT_ID(N'[dbo].[CAS]')
	AND [types].[name] IN ('varchar', 'char', 'nvarchar', 'nchar', 'varbinary', 'binary') AND [columns].[max_length] > 0

SELECT @SQL = [_DB].[String_FormatXML](
	N'CREATE TRIGGER %trigger
	ON %view
	INSTEAD OF INSERT, UPDATE
	AS
	BEGIN
		SET NOCOUNT ON

		SET ROWCOUNT %count

		DECLARE @Result [xml] SELECT @Result = (
			SELECT
				*
			FROM
				(
					SELECT TOP 0
						CONVERT([sysname], NULL) AS ''@name'',
						CONVERT([int], NULL) AS ''@len'',
						CONVERT([int], NULL) AS ''@max'',
						CONVERT([nvarchar](max), NULL) AS ''text()''

					%sql
				) AS [Inner]

			FOR
				XML PATH(''column'')
		)

		SET ROWCOUNT 0

		IF @Result IS NOT NULL BEGIN
			EXECUTE [_DB].[Log_Write]
				@@Type = NULL, --TODO:Register type
				@@Message = ''String or binary truncated'',
				@@Details = @Result
		END

	END',
	(
		SELECT
			@@TriggerName AS [trigger],
			@@ViewName AS [view],
			@@Errors AS [count],
			@SQL as [sql]
		FOR
			XML PATH('')
	)
)

EXECUTE(@SQL)

RETURN 0
GO




